package com.sino.dataworks.util;

import com.sino.dataworks.vo.TableDescVo;
import com.sino.dataworks.vo.TableFieldVo;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.ComparisonOperator;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import org.apache.calcite.sql.*;
import org.apache.calcite.sql.parser.SqlParseException;
import org.apache.calcite.sql.parser.SqlParser;


import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * @author
 * @version 1.0
 * @className: SqlParserUtils
 * @description: SQL解析工具类
 * @date: 2022/12/11 16:10
 **/
public final class SqlParserUtils {



    public static List<TableDescVo> getTablesBySQL(String sql) throws JSQLParserException {
        Select select = (Select) CCJSqlParserUtil.parse(sql);
        List<TableDescVo> tableList = new ArrayList<>();

        // 处理 WITH 子句中的 CTE
        if (select.getWithItemsList() != null) {
            for (WithItem withItem : select.getWithItemsList()) {
                extractTablesFromSelectBody(withItem.getSubSelect().getSelectBody(), tableList);
            }
        }

        // 处理主查询中的表
        extractTablesFromSelectBody(select.getSelectBody(), tableList);

        return tableList;
    }
    private static void extractTablesFromWithItems(List<WithItem> withItems, List<TableDescVo> tableList) {
        if (withItems == null) return;
        for (WithItem withItem : withItems) {
            extractTablesFromSelectBody(withItem.getSubSelect().getSelectBody(), tableList);
            // 提取 CTE 自身的表名
            TableDescVo cteTable = new TableDescVo();
            cteTable.setOriginName(withItem.getName());
            cteTable.setName(withItem.getName());
            tableList.add(cteTable);
        }
    }
    private static void extractTablesFromSelectBody(SelectBody selectBody, List<TableDescVo> tableList) {
        if (selectBody instanceof PlainSelect) {
            PlainSelect plainSelect = (PlainSelect) selectBody;
            extractTablesFromFromItem(plainSelect.getFromItem(), tableList);

            if (plainSelect.getJoins() != null) {
                for (Join join : plainSelect.getJoins()) {
                    extractTablesFromFromItem(join.getRightItem(), tableList);
                }
            }

            // 处理子查询
            if (plainSelect.getWhere() != null && plainSelect.getWhere() instanceof SubSelect) {
                extractTablesFromSelectBody(((SubSelect) plainSelect.getWhere()).getSelectBody(), tableList);
            }

            // 处理 SELECT 子句中的子查询
            List<SelectItem> selectItems = plainSelect.getSelectItems();
            for (SelectItem item : selectItems) {
                if (item instanceof SelectExpressionItem) {
                    Expression expression = ((SelectExpressionItem) item).getExpression();
                    if (expression instanceof SubSelect) {
                        extractTablesFromSelectBody(((SubSelect) expression).getSelectBody(), tableList);
                    }
                }
            }
        } else if (selectBody instanceof SetOperationList) {
            SetOperationList setOpList = (SetOperationList) selectBody;
            for (SelectBody body : setOpList.getSelects()) {
                extractTablesFromSelectBody(body, tableList);
            }
        }
    }

    private static void extractTablesFromFromItem(FromItem fromItem, List<TableDescVo> tableList) {
        if (fromItem instanceof Table) {
            Table table = (Table) fromItem;
            TableDescVo tableDesc = new TableDescVo();
            tableDesc.setOriginName(table.getName());
            tableDesc.setName(table.getAlias() != null ? table.getAlias().getName() : table.getName());
            tableList.add(tableDesc);
        } else if (fromItem instanceof SubSelect) {
            extractTablesFromSelectBody(((SubSelect) fromItem).getSelectBody(), tableList);
        } else if (fromItem instanceof SubJoin) {
            SubJoin subJoin = (SubJoin) fromItem;
            extractTablesFromFromItem(subJoin.getLeft(), tableList);
            if (subJoin.getJoinList() != null) {
                for (Join join : subJoin.getJoinList()) {
                    extractTablesFromFromItem(join.getRightItem(), tableList);
                }
            }
        }
    }


    /**
     * 通过sql获得所有返回参数结果
     *
     * @param sql
     * @return columnList: SQL列
     * @throws JSQLParserException
     */
    public static List<TableFieldVo> getColumnNameBySQL(String sql) throws JSQLParserException {
        Select select = (Select) CCJSqlParserUtil.parse(sql);
        SelectBody selectBody = select.getSelectBody();
        List<TableFieldVo> columnList = new ArrayList<>();
        List<SelectItem> selectItems = ((PlainSelect) selectBody).getSelectItems();
        for (SelectItem item : selectItems) {
            TableFieldVo tableField = new TableFieldVo();
            String column = null;
            if (item instanceof AllColumns) {
                column = item.toString();
            }
            if (item instanceof AllTableColumns) {
                Table table = ((AllTableColumns) item).getTable();
                tableField.setTableName(table.getName());
                column = "*";
            }
            if (item instanceof SelectExpressionItem) {
                Alias alias = ((SelectExpressionItem) item).getAlias();
                Expression expression = ((SelectExpressionItem) item).getExpression();
                if (alias != null) {
                    // 原名称
                    column = expression.toString();
                    // 别名
                    String aliasName = alias.getName();
                    tableField.setAliasName(aliasName);
                } else if (expression != null) {
                    column = expression.toString();
                    if (column.contains(".")) {
                        String[] columnArr = column.split("\\.");
                        tableField.setTableName(columnArr[0]);
                        column = columnArr[1];
                    }
                }
            }
            tableField.setFieldName(column);
            columnList.add(tableField);
        }
        return columnList;
    }


    /**
     * 获得jdbcName参数形式请求参数
     *
     * @param sql
     * @return
     * @throws JSQLParserException
     */
    public static List<TableFieldVo> getRequestParamsBySQL(String sql) throws JSQLParserException {
        Select select = (Select) CCJSqlParserUtil.parse(sql);
        SelectBody selectBody = select.getSelectBody();
        List<TableFieldVo> requestParamList = new ArrayList<>();
        Expression where = ((PlainSelect) selectBody).getWhere();
        if (where != null) {
            extractedRequestParams(where, requestParamList);
        }
        Offset offset = ((PlainSelect) selectBody).getOffset();
        if (offset != null && offset.getOffset() != null) {
            offset.getOffset().accept(new ExpressionVisitorAdapter() {
                @Override
                public void visit(JdbcNamedParameter namedParameter) {
                    TableFieldVo tableField = new TableFieldVo();
                    tableField.setFieldName(namedParameter.getName());
                    requestParamList.add(tableField);
                }
            });
        }
        Limit limit = ((PlainSelect) selectBody).getLimit();
        if (limit != null) {
            Expression rowCount = limit.getRowCount();
            if (rowCount != null) {
                rowCount.accept(new ExpressionVisitorAdapter() {
                    @Override
                    public void visit(JdbcNamedParameter namedParameter) {
                        TableFieldVo tableField = new TableFieldVo();
                        tableField.setFieldName(namedParameter.getName());
                        requestParamList.add(tableField);
                    }
                });
            }
        }
        return requestParamList;
    }

    /**
     * 提取查询参数: 解析查询语法树，提取嵌套子查询查询条件
     *
     * @param where
     * @param requestParamList
     */
    private static void extractedRequestParams(Expression where, List<TableFieldVo> requestParamList) {
        // 这里需要语法树反向解析
        if (where instanceof AndExpression || where instanceof OrExpression) {
            // 说明where为and、or连接
            Expression leftExpression = ((BinaryExpression) where).getLeftExpression();
            extractedRequestParams(leftExpression, requestParamList);
            Expression rightExpression = ((BinaryExpression) where).getRightExpression();
            extractedRequestParams(rightExpression, requestParamList);
        }else if (where instanceof ComparisonOperator || where instanceof InExpression || where instanceof LikeExpression) {
            // where为比较符,取右侧元素
            Expression rightExpression = where;
            if(where instanceof ComparisonOperator){
                rightExpression = ((ComparisonOperator) where).getRightExpression();
            }else if(where instanceof InExpression){
                rightExpression = ((InExpression) where).getRightExpression();
            }else{
                rightExpression = ((LikeExpression) where).getRightExpression();
            }
            if (rightExpression instanceof SubSelect) {
                // 说明为子嵌套查询
                PlainSelect subSelectBody = (PlainSelect) ((SubSelect) rightExpression).getSelectBody();
                // 提取查询条件
                Expression childWhere = subSelectBody.getWhere();
                extractedRequestParams(childWhere, requestParamList);
            }else{
                where.accept(new ExpressionVisitorAdapter() {
                    @Override
                    public void visit(JdbcNamedParameter namedParameter) {
                        TableFieldVo tableField = new TableFieldVo();
                        tableField.setFieldName(namedParameter.getName());
                        requestParamList.add(tableField);
                    }
                });
            }
        }

    }



    public static void main(String[] args) {
        StringBuffer sql = new StringBuffer();
        sql.append("" +
                "WITH\n" +
                "    t03_loan_due_dtl_month_mar_tmp AS ( SELECT tdmt.loan_due_no , tdmt.cust_request_no , tdmt.dec_cnt , tdmt.loan_type , tdmt.total_issues , tdmt.loan_issue_date , tdmt.close_date , tdmt.loan_status , tdmt.close_method , tdmt.loan_term_ori , tdmt.whether_discount_interest , tdmt.city_name_dealer , tdmt.province_dealer , tdmt.dealer_province , tdmt.dealer_city , tdmt.discount_amount , tdmt.af_flag , tdmt.approved_date , tdmt.cust_type , tdmt.is_commercial , tdmt.remark4 , tdmt.data_dt FROM DatawareHouse_Pro.dws_t03_loan_due_dtl_month tdmt WHERE TDMT.LOAN_STATUS = '1' AND NOT EXISTS( SELECT 1 FROM DatawareHouse_Pro.dws_t03_loan_due_dtl tdd WHERE tdd.loan_due_no = tdmt.loan_due_no AND tdd.ds='${TXDATE}') AND tdmt.cust_type = 'CP000004' AND tdmt.ds=to_char(to_date(add_months(LASTDAY(TO_DATE('${TXDATE}','yyyymmdd')),-1),'yyyy-mm-dd'),'yyyymmdd') )\n" +
                "    ,f_retl_loan_tmp1 AS ( SELECT tlai.cust_request_no AS cust_request_no , tlai.sp_child_acctno AS sp_child_acctno , tlai.cust_id AS cust_id , tlai.car_group AS license_plate_number , tlai.salesadviser AS salesadviser , tlai.consultanttel AS consultanttel , tlai.car_type AS car_type , decode(tlai.is_affiliated, '0', '否', '1', '是') AS is_affiliated , CASE WHEN tlai.MORTGAGE_TYPE = 'CZ' THEN '出租车-公司' WHEN tlai.MORTGAGE_TYPE = 'CZP' THEN '出租车-个人' WHEN tlai.MORTGAGE_TYPE = 'CP' THEN '出租车-新车公司牌照' WHEN tlai.MORTGAGE_TYPE = 'JX' THEN '驾校' WHEN tlai.MORTGAGE_TYPE = 'CZXGSPZ' THEN '出租车-新车上公司牌照' WHEN tlai.MORTGAGE_TYPE = 'YYXGSPZ' THEN '营运-新车上公司牌照' WHEN tlai.MORTGAGE_TYPE = 'FYYXGSPZ' THEN '非营运-新车上公司牌照' WHEN tlai.MORTGAGE_TYPE = 'WY' THEN '网约车-个人' WHEN tlai.MORTGAGE_TYPE = 'WQ' THEN '网约车-公司' WHEN tlai.MORTGAGE_TYPE = 'QW' THEN '轻卡轻客微面微卡中卡重卡' WHEN tlai.MORTGAGE_TYPE = 'GT' THEN '个体工商户(新车)' WHEN tlai.MORTGAGE_TYPE = 'SC' THEN '新车上公司牌照' WHEN tlai.MORTGAGE_TYPE = 'WPP' THEN '网约车-新车上公司牌照' WHEN tlai.MORTGAGE_TYPE = 'QT' THEN '其它(新车)' WHEN tlai.MORTGAGE_TYPE = 'WPH' THEN '网约车-个体工商户' WHEN tlai.MORTGAGE_TYPE = 'XP' THEN '新能源商用车-新车上公司牌照' WHEN tlai.MORTGAGE_TYPE = 'WPPYZ' THEN '网约车-新车上公司牌照( 优质)' WHEN tlai.MORTGAGE_TYPE = 'H' THEN '网约车-个人(准新车)' WHEN tlai.MORTGAGE_TYPE = 'M' THEN '网约车-公司(准新车)' WHEN tlai.MORTGAGE_TYPE = 'G' THEN '轻型商用车(准新车)' WHEN tlai.MORTGAGE_TYPE = 'I' THEN '出租车(准新车)' WHEN tlai.MORTGAGE_TYPE = 'J' THEN '个体工商户(二手车)' WHEN tlai.MORTGAGE_TYPE = 'K' THEN '二手车上公司牌照' WHEN tlai.MORTGAGE_TYPE = 'L' THEN '其它(二手车)' WHEN tlai.MORTGAGE_TYPE = 'QWU' THEN '轻卡轻客微面微卡中卡重卡(二手车)' WHEN tlai.MORTGAGE_TYPE = 'OWPH' THEN '网约车-个体工商户' WHEN tlai.MORTGAGE_TYPE = 'OWPP' THEN '网约车-新车上公司牌照' WHEN tlai.MORTGAGE_TYPE = 'QWGR' THEN '网约车-个人' WHEN tlai.MORTGAGE_TYPE = 'QWGS' THEN '网约车-公司' WHEN tlai.MORTGAGE_TYPE = 'CZGR' THEN '出租车-个人' WHEN tlai.MORTGAGE_TYPE = 'CZGS' THEN '出租车-公司' WHEN tlai.MORTGAGE_TYPE = 'CZUGSPZ' THEN '出租车-二手车上公司牌照' WHEN tlai.MORTGAGE_TYPE = 'YYUGSPZ' THEN '营运-二手车上公司牌照' WHEN tlai.MORTGAGE_TYPE = 'FYYUGSPZ' THEN '非营运-二手车上公司牌照' ELSE '无' END MORTGAGE_TYPE , CASE WHEN tlai.mortgage_type = 'G' THEN '是 ' ELSE '否' END AS light_commercial_flag , tlai.affiliated_units AS affiliated_units , decode(tlai.loan_vehicle_type, '1', '是', '2', '否', '') AS is_new_car , tlai.application_id AS application_id , tlai.wx_authentication_id AS wx_authentication_id , tlai.seller_name AS seller_name , tlai.car_city_code AS car_city_code , tlai.car_province_code AS car_province_code , tlai.drawer_name AS drawer_name , CASE WHEN tlai.cust_rate=0 THEN 0.0000 ELSE TRUNC_NUM(CAST (tlai.cust_rate AS DOUBLE) , 4) END AS init_rate , tlai.second_network AS second_network , tlai.manufacturer_name AS manufacturer_name , tlai.eos_score AS eos_score , tlai.risk_level AS risk_level , to_date(substr(tlai.approval_date, 1, 10), 'yyyy-mm-dd') AS approved_date , CASE WHEN tlai.credit_approval_senior_manager IS NOT NULL THEN '三级审批' WHEN tlai.credit_approval_senior_manager IS NULL AND tlai.credit_approval_manager IS NOT NULL THEN '二级审批' WHEN tlai.credit_approval_senior_manager IS NULL AND tlai.credit_approval_manager IS NULL AND tlai.credit_approval_assistant IS NOT NULL THEN '一级审批' ELSE '' END AS approval_level , CASE WHEN tlai.approval_status IN ('999', '994', '996') THEN '人工审批' WHEN tlai.credit_approval_assistant IS NULL THEN '自动审批' ELSE '人工审批' END AS approval_way , CASE WHEN tlai.approval_status = '000' THEN '待发起' WHEN tlai.approval_status = '001' THEN '已放款未付款' WHEN tlai.approval_status = '002' THEN '已付款' WHEN tlai.approval_status = '003' THEN '零售放款终止' WHEN tlai.approval_status = '004' THEN '放款未审批' WHEN tlai.approval_status = '991' THEN '提交前撤销' WHEN tlai.approval_status = '993' THEN '撤销' WHEN tlai.approval_status = '994' THEN '审批前撤销' WHEN tlai.approval_status = '995' THEN '审批后撤销' WHEN tlai.approval_status = '996' THEN '零售信审终止' WHEN tlai.approval_status = '997' THEN '已审批未放款' WHEN tlai.approval_status = '998' THEN '拒绝' WHEN tlai.approval_status = '999' THEN '已申请' END AS apply_approval_status , NULL AS approval_result , CASE WHEN tlai.approval_status IN ('995', '997', '001', '002', '003', '004') THEN '是' WHEN tlai.approval_status = '998' THEN '否' ELSE '其他' END AS is_approved , tlai.credit_approval_assistant AS approval_name , tlai.credit_approval_manager AS approval_manager , tlai.credit_approval_senior_manager AS approval_senior_manager , tlai.assistant_apply_opinion AS assistant_apply_opinion , tlai.manager_apply_opinion AS manager_apply_opinion , tlai.senior_manager_apply_opinion AS senior_manager_apply_opi , tlai.is_conditional_1 AS is_conditional , decode(tlai.approval_biz_type, '1', '新申请', '2', '重审', '3', '补资料', '4', '改车价', '') AS is_retrial , to_date(substr(tlai.teller_date, 1, 10), 'yyyy-mm-dd') AS reject_processing_date , tlai.reason AS reason , tlai.reject_fst AS fir_level_reject_reason_op , tlai.reject_scd AS sec_level_reject_reason_op , CASE WHEN ( CASE WHEN regexp_count(regexp_replace(tlai.assistant_apply_opinion, '同意-|批准-', ''), '通过|同意|批准')>0 THEN '同意' WHEN regexp_count(regexp_replace(tlai.assistant_apply_opinion, '同意-|批准-', ''), '问题|拒绝|补资料')>0 THEN '拒绝' ELSE regexp_replace(tlai.assistant_apply_opinion, '同意-|批准-', '') END) = ( CASE WHEN regexp_count(regexp_replace(tlai.manager_apply_opinion, '同意-|批准-', ''), '建议附条件|批准|通过|同意')>0 THEN '同意' WHEN regexp_count(regexp_replace(tlai.manager_apply_opinion, '同意-|批准-', ''), '拒绝|退回')>0 THEN '拒绝' ELSE regexp_replace(tlai.manager_apply_opinion, '同意-|批准-', '') END) THEN '是' ELSE '否' END AS is_approval_opinions_consist , CASE WHEN tlai.loan_vehicle_type= '1' THEN '新车' WHEN t03rli.product_name LIKE '%准新车%' OR t03rli.product_name LIKE '%ZXC%' OR t03rli.product_name LIKE '%奇瑞体系二手车%' OR t03rli.product_name LIKE '%预上牌%' OR t03rli.product_name LIKE '%二手车-至优%' OR t03rli.product_name LIKE '%二手车-至尊%' THEN '准新车' ELSE '二手车' END AS loan_type , CASE WHEN tlai.invest_trade = '9045' THEN '农、林、牧、渔业' WHEN tlai.invest_trade = '9046' THEN '采矿业' WHEN tlai.invest_trade = '9047' THEN '制造业' WHEN tlai.invest_trade = '9048' THEN '电力、燃气及水的生产和供应业' WHEN tlai.invest_trade = '9049' THEN '建筑业' WHEN tlai.invest_trade = '9050' THEN '交通运输、仓储和邮政业' WHEN tlai.invest_trade = '9051' THEN '信息传输、计算机服务和软件业' WHEN tlai.invest_trade = '9052' THEN '批发和零售业' WHEN tlai.invest_trade = '9053' THEN '住宿和餐饮业' WHEN tlai.invest_trade = '9054' THEN '金融业' WHEN tlai.invest_trade = '9055' THEN '房地产业' WHEN tlai.invest_trade = '9056' THEN '租赁和商务服务业' WHEN tlai.invest_trade = '9057' THEN '科学研究、技术服务业和地质勘察业' WHEN tlai.invest_trade = '9058' THEN '水利、环境和公共设施管理业' WHEN tlai.invest_trade = '9065' THEN '居民服务和其他服务业' WHEN tlai.invest_trade = '9066' THEN '教育' WHEN tlai.invest_trade = '9067' THEN '卫生、社会保障和社会福利业' WHEN tlai.invest_trade = '9068' THEN '文化、体育和娱乐业' WHEN tlai.invest_trade = '9069' THEN '公共管理和社会组织' WHEN tlai.invest_trade = '9070' THEN '国际组织' END AS invest_trade , t03rli.order_id AS loan_due_no , tldd.vehicle_brand_name AS car_stylie_name , CASE WHEN tldd.car_app_amount=0 THEN '0.0000' ELSE TRUNC_NUM(CAST(tldd.car_app_amount AS DOUBLE ),4) END AS car_app_amount , tldd.loan_issue_date AS loan_issue_date , CASE WHEN tldd.is_commercial = '1' THEN '商用' WHEN tldd.is_commercial = '0' THEN '消费' END AS commercial , to_date(substr(tldd.approved_date, 1, 10), 'yyyy-mm-dd') AS loan_approval_date , CASE WHEN tldd.loan_status = '1' THEN to_date(to_char(tldd.close_date, 'yyyy-mm-dd'), 'yyyy-mm-dd') END AS close_date , decode(tldd.close_method, '0', '未结清', '1', '正常结清', '2', '提前还款结清', '3', '转呆后结清', '4', '逾期结清', '5', '核销后结清', '其他') AS close_method , tldd.loan_term_ori AS loan_term_ori , CASE WHEN tldd.selling_price='0' THEN '0.0000' ELSE TRUNC_NUM( CAST (tldd.selling_price AS DOUBLE ), 4) END AS selling_price , CASE WHEN tldd.cat_coach_amount='69900.00元' THEN '69900.0000' ELSE TRUNC_NUM( CAST (tldd.cat_coach_amount AS DOUBLE ), 4) END AS cat_coach_amount , decode(tldd.whether_discount_interest, '1', '是', '0', '否') AS is_discount_interest , CASE WHEN tldd.discount_amount='0' THEN '0.0000' ELSE TRUNC_NUM( CAST (tldd.discount_amount AS DOUBLE ), 4) END AS discount_amount , decode(tldd.af_flag, '0', '否', '1', '是') AS af_flag_cus , tldd.first_registration_date AS first_registration_date , tldd.dec_cnt AS dec_cnt , CASE WHEN nvl(tldd.cust_type, 'CP000004') = 'CP000004' THEN '零售客户' WHEN nvl(tldd.cust_type, 'CP000004') = 'CP000005' THEN '经销商客户' WHEN nvl(tldd.cust_type, 'CP000004') = 'CP000001' THEN '机构客户' WHEN nvl(tldd.cust_type, 'CP000004') = 'CP000006' THEN '融资租赁客户' WHEN nvl(tldd.cust_type, 'CP000004') = 'RP000001' THEN '新融资租赁客户' END AS cust_type , CASE WHEN tldd.remark4 = '1' THEN '抵押担保贷款' WHEN tldd.remark4 = '2' THEN '质押担保贷款' WHEN tldd.remark4 = '3' THEN '保证担保贷款' WHEN tldd.remark4 = '4' THEN '信用担保贷款' END AS gurantee_tpye , TO_CHAR(TO_DATE('${TXDATE}','YYYYMMDD'),'yyyy-mm-dd') AS DATA_DT , nvl(t03lapi1.THSL, 0) AS return_times , nvl(t03lapi1.DHWJTSL, '0') AS return_times_not_phocon , CASE WHEN t03rli.manufacturer IN ('9', '248', '250') AND tlai.energe_cd_vhcl = '34' THEN '奇瑞新能源' WHEN t03rli.manufacturer IN ('9', '248', '250') THEN '奇瑞品牌' WHEN t03rli.manufacturer = '249' AND tlai.energe_cd_vhcl = '34' THEN '开瑞新能源' WHEN t03rli.manufacturer = '249' THEN '开瑞品牌' WHEN t03rli.manufacturer = '324' THEN '凯翼品牌' WHEN t03rli.manufacturer = '308' THEN '观致品牌' WHEN t03rli.manufacturer = '326' THEN '奇瑞捷豹路虎' WHEN t03rli.manufacturer = '420' THEN '捷途品牌' WHEN t03rli.manufacturer = '462' THEN '星途品牌' ELSE '多品牌' END AS car_brand , t03rli.automotive_styling_name AS model_name , t03rli.frame_number AS frame_number , t03rli.franchiser_id AS dealer_no , t03rli.group_name AS dealer_name , to_date(substr(t03rli.apply_time, 1, 10), 'yyyy-mm-dd') AS apply_time , t03rli.apply_loan_term AS total_issues , TRUNC_NUM( CAST (t03rli.down_payment_ratio_total AS DOUBLE ), 6) AS down_payment_ratio , CASE WHEN t03rli.loan_amount_total='0' THEN '0.0000' ELSE TRUNC_NUM( CAST(t03rli.loan_amount_total AS DOUBLE ), 4) END AS iou_amt , CASE WHEN t03rli.division_loan_amount='0' THEN '0.0000' ELSE TRUNC_NUM( CAST ( t03rli.division_loan_amount AS DOUBLE ), 4) END AS iou_amt_qr , CASE WHEN t03rli.repayment_way = 'QR_JC_HuanKuanFangShi_DEBX' THEN '等额本息' WHEN t03rli.repayment_way = 'QR_JC_HuanKuanFangShi_DEBJ' THEN '等额本金' WHEN t03rli.repayment_way = 'QR_JC_HuanKuanFangShi_BLDEBX' THEN '百龙等额本息' WHEN t03rli.repayment_way = 'QR_JC_HuanKuanFangShi_BLDEBJ' THEN '百龙等额本金' WHEN t03rli.repayment_way = 'QR_JC_HuanKuanFangShi_HBFX' THEN '到期一次性还本付息' WHEN t03rli.repayment_way = 'QR_JC_HuanKuanFangShi_HBAYFX' THEN '到期一次性还本按月付息' ELSE '未匹配' END AS payment_method , t03rli.product_name AS product_name , decode(t03rli.agent_jb_and_lh, '1', '是', '0', '否') AS agent_jb_and_lh , CASE WHEN t03rli.product_name LIKE '%全额担保%' AND tldd.loan_type = '2' THEN '是' ELSE '否' END AS is_used_car_ful_guaranteed , CASE WHEN t03rli.ec_loan_amount='0' THEN '0.0000' ELSE TRUNC_NUM( CAST (t03rli.ec_loan_amount AS DOUBLE ), 4) END AS ec_loan_amount , CASE WHEN t03rli.loan_amount='0' THEN '0.0000' ELSE TRUNC_NUM( CAST (t03rli.loan_amount AS DOUBLE ), 4) END AS car_loan_amount , CASE WHEN t03rli.loan_amount_qy='0' THEN '0.0000' ELSE TRUNC_NUM( CAST (t03rli.loan_amount_qy AS DOUBLE ), 4) END AS car_loan_amount_qr , t03rli.flag_synd AS joint_loan_flag , t03rli.join_loan_bank_name AS joint_loan_bank_name , t03rli.is_new_energy AS new_energy_flag , t01di.provn_cd AS dealer_province_id , t01di.city_cd AS dealer_city_id , t01di.sale_region_nm AS area_nm , tlai.cust_name AS cust_name  ,coalesce(ccri.enterprise_subject, '01') AS enterprise_subject ,coalesce(occria.order_source, '01') AS order_source ,coalesce(oclvi.product_subject, '01') AS product_subject FROM DatawareHouse_Pro.dws_t03_loan_application_info tlai LEFT JOIN ( SELECT instance_id AS cust_request_no,coalesce(enterprise_subject, '01') AS enterprise_subject FROM stg_b_apply_loan_info WHERE ds = '${TXDATE}' UNION SELECT cust_request_no,coalesce(enterprise_subject, '01') AS enterprise_subject FROM ods_ccm_credit_result_info WHERE ds = '${TXDATE}' ) ccri ON ccri.cust_request_no = tlai.cust_request_no LEFT JOIN ods_cab_b_apply_loan_info occria ON occria.cust_request_no = tlai.cust_request_no AND occria.ds = '${TXDATE}' LEFT JOIN ods_ccm_loan_vehicle_info oclvi ON oclvi.cust_request_no = tlai.cust_request_no AND oclvi.ds = '${TXDATE}' LEFT JOIN ( SELECT tpm.* , vehicle_brand_name , car_app_amount , selling_price , cat_coach_amount , first_registration_date FROM ( SELECT loan_due_no , cust_request_no , dec_cnt , loan_type , total_issues , loan_issue_date , close_date , loan_status , close_method , loan_term_ori , whether_discount_interest , city_name_dealer , province_dealer , dealer_province , dealer_city , discount_amount , af_flag , approved_date , cust_type , is_commercial , remark4 FROM DatawareHouse_Pro.dws_t03_loan_due_dtl WHERE cust_type IN ('CP000004', 'CP000006', 'RP000001') AND ds='${TXDATE}' UNION ALL SELECT loan_due_no , cust_request_no , dec_cnt , loan_type , total_issues , loan_issue_date , close_date , loan_status , close_method , loan_term_ori , whether_discount_interest , city_name_dealer , province_dealer , dealer_province , dealer_city , discount_amount , af_flag , approved_date , cust_type , is_commercial , remark4 FROM t03_loan_due_dtl_month_mar_tmp b WHERE loan_status = '1' AND NOT EXISTS( SELECT 1 FROM dws_t03_loan_due_dtl a WHERE a.loan_due_no = b.loan_due_no AND a.ds='${TXDATE}' ) AND cust_type IN ('CP000004', 'CP000006', 'RP000001')) tpm LEFT JOIN ( SELECT loan_due_no , vehicle_brand_name , car_app_amount , selling_price , cat_coach_amount , first_registration_date FROM DatawareHouse_Pro.dws_t01_car_info WHERE ds='${TXDATE}') tci ON tpm.loan_due_no = tci.loan_due_no ) tldd ON tlai.cust_request_no = tldd.cust_request_no LEFT JOIN DatawareHouse_Pro.dws_t03_retl_loan_info t03rli ON t03rli.cust_request_no = tlai.cust_request_no AND t03rli.ds='${TXDATE}' LEFT JOIN DatawareHouse_Pro.dws_t02_dealer_info t01di ON t01di.dealer_no = t03rli.franchiser_id AND t01di.ds='${TXDATE}' LEFT JOIN ( SELECT cust_request_no , sum(CASE WHEN regexp_count(reason, '退回')>0 THEN 1 ELSE 0 END) AS thsl , sum(CASE WHEN NOT regexp_count(reason, '电话未接通')>0 THEN 1 ELSE 0 END) AS dhwjtsl FROM DatawareHouse_Pro.dws_t03_loan_application_info WHERE ds='${TXDATE}' AND (regexp_count(reason, '退回')>0 OR NOT regexp_count(reason, '电话未接通')>0) GROUP BY cust_request_no ) t03lapi1 ON t03lapi1.cust_request_no = tldd.cust_request_no WHERE tlai.cust_request_no IS NOT NULL AND tlai.ds='${TXDATE}' AND tlai.approval_status <> '991' AND tlai.approval_status <> '000' )    --申请编号                                               --sp子账号     --客户号    --车牌号    --销售顾问    --顾问电话    --车身类型    --是否挂靠    --挂靠类型    --是否轻型商用车    --挂靠单位    --是否新车    --app申请编号                                        --微信授权编号    --销售方名称    --销售方城市    --销售方省份    --开票方名称    --执行利率    --进件二网    --生产厂家    --评分卡分值    --评分卡风险等级                        --审批日期    --审批层级    --审批方式(自动审批/人工审批)   231116将'999','994','996'归为'人工审批'    --审批状态(申请状态)    --审批结果    --是否审批通过                                    --信贷助理    --信贷经理    --高级信贷经理    --信贷助理意见    --信贷经理意见    --高级信贷经理意见    --是否附条件    --是否重审    --拒绝处理日期                        --报告中拒绝原因描述     --一级拒绝原因选项 --新增    --二级拒绝原因选项  --新增    --审批意见是否一致   --新增  --231107调整，原来是zxc(小写),将其调整为ZXC(大写)    --贷款类型                              -- 单位所属行业    --借据号 20240608 tldd.loan_due_no-->t03rli.order_id    --车系    --车辆价格    --放款时间    --申请用途(车辆用途)    --放款审批时间    --结清日期                                    --结清类型    --贷款期限    --全国成交价    --车辆官方指导价    --是否贴息    --贴息金额    --是否欺诈客户    --车辆首次登记日期    --已还款期数                                        --客户类型    --担保方式    --退回次数    --退回原因非”电话未接通“退回次数    --品牌区分    --购买车型    --车架号    --经销商编号    --经销商名    --申请时间 (创建时间)                               --申请贷款期数    --合计首付比例    --合计贷款金额    --合同金额(奇瑞金融)    --  还款方式    --产品名称    --是否直接合作捷豹路虎    --是否二手车全额担保                                --安心贷服务包贷款金额    --车辆贷款总金额    --车辆贷款金额奇瑞    --联合贷款标识    --联合贷银行    --是否新能源    --经销商省份id               --经销商城市id    --大区名称     --客户名称\n" +
                "  \n" +
                "    ,f_retl_loan_tmp2 AS ( SELECT max_overdueday AS max_overdueday , overdue_day, a.past_due , a.norepay_day , a.loan_due_no , a.dlay_count , a.overdue_date , a.over_date_term FROM DatawareHouse_Pro.dws_t03_loan_overdue_dtl a LEFT JOIN ( SELECT max(max_overdueday) AS max_overdueday ,loan_due_no FROM dws_t03_service_overdue_dtl WHERE ds='${TXDATE}' GROUP BY loan_due_no ) b ON a.loan_due_no = b.loan_due_no LEFT JOIN ( SELECT overdue_day ,loan_due_no FROM ( SELECT loan_due_no , DATA_DT , overdue_day , ROW_NUMBER() OVER (PARTITION BY LOAN_DUE_NO ORDER BY DATA_DT DESC ) AS RN FROM dws_t03_service_overdue_dtl WHERE ds='${TXDATE}' ) WHERE rn='1' ) c ON a.loan_due_no = c.loan_due_no WHERE a.ds='${TXDATE}' )\n" +
                "    ,f_retl_loan_tmp3 AS ( SELECT max(txn_date) AS txn_date , loan_due_no FROM DatawareHouse_Pro.dws_t04_loan_repay_dtl_di WHERE cust_type = 'CP000004' AND ds<='${TXDATE}' GROUP BY loan_due_no )\n" +
                "    ,f_retl_loan_tmp4 AS ( SELECT loan_due_no ,due_date FROM ( SELECT loan_due_no , row_number() OVER (PARTITION BY loan_due_no ORDER BY due_date ASC) rn , due_date FROM DatawareHouse_Pro.dws_t04_loan_repay_plan WHERE cust_type = 'CP000004' AND ds='${TXDATE}' ) WHERE rn = '1' )\n" +
                "    ,main AS ( SELECT tlai.cust_request_no ,tlai.loan_due_no ,tlai.cust_id ,tlai.dealer_no ,tlai.dealer_name ,tlai.area_nm AS area_name_dealer ,tlai.apply_time ,tlai.loan_issue_date ,tlai.seller_name ,tlai.car_city_code ,tlai.car_province_code ,tlai.drawer_name ,tlod.max_overdueday ,tlod.overdue_day ,tlod.past_due ,tlod.norepay_day ,NVL2(tlod.loan_due_no, '是', '否') AS current_overdue ,NVL2(tlod.loan_due_no, '是', '否') AS is_history_overdue ,tlod.dlay_count ,TO_DATE(tlod.overdue_date, 'YYYY-MM-DD') AS overdue_date ,DECODE(tlod.over_date_term, '1', '是', '否') AS is_first_ins_overdue ,NVL2(tlod.overdue_date, '', DATEDIFF(tlrd.txn_date,TO_DATE(TLOD.OVERDUE_DATE, 'YYYY-MM-DD'))) AS overdue_repayment_days ,CASE WHEN t07ldt.transaction_date IS NOT NULL THEN DATEDIFF(TO_DATE(REPLACE(tlai.data_dt,'-',''), 'YYYYMMDD') ,t07ldt.transaction_date) ELSE NVL(DATEDIFF(TO_DATE(REPLACE(tlai.data_dt,'-',''), 'YYYYMMDD'),tlrp.due_date), '0') END AS f_pay_days ,tlai.application_id ,tlai.cust_name ,tlai.return_times ,tlai.return_times_not_phocon ,tlai.second_network  ,tlai.enterprise_subject ,tlai.order_source ,tlai.product_subject FROM f_retl_loan_tmp1 tlai LEFT JOIN f_retl_loan_tmp2 tlod ON tlai.loan_due_no = tlod.loan_due_no LEFT JOIN f_retl_loan_tmp3 tlrd ON tlai.loan_due_no = tlrd.loan_due_no LEFT JOIN dws_t07_loan_due_trans_dt t07ldt ON tlai.loan_due_no=t07ldt.ln_cod AND t07ldt.ds='${TXDATE}' LEFT JOIN F_RETL_LOAN_TMP4 TLRP ON tlai.loan_due_no=tlrp.loan_due_no )    --\t申请编号                                       --\t借据号    --\t客户号    --\t经销商编号    --\t经销商名称    --大区    --\t申请时间    --\t放款时间     -- 销售方名称    -- 销售方城市    -- 销售方省份                               -- 开票方名称    -- 最大逾期天数    -- 当前逾期天数    -- 当前逾期金额    -- 连续未还款天数    -- 当前是否逾期    -- 历史是否逾期    -- 历史累计逾期次数                         -- 当前逾期日期    -- 是否首期即逾期    -- 逾期后第几天还款    --连续未还款天数    --客户姓名\n" +
                " \n" +
                "    ,OAA AS ( SELECT * FROM ( SELECT new_apply_no AS new_request_no ,row_number() OVER(PARTITION BY new_apply_no ORDER BY retrial_tm DESC ) rn FROM dwd_rt_pre_eos_retrial_info WHERE ds='${TXDATE}' AND retrial_typ='2' ) WHERE rn='1' )    --DatawareHouse_Pro.ods_ccm_again_apply_relation_info     --231115新增  用于\"是否重审\"\n" +
                "\n" +
                "    ,crip AS ( SELECT * FROM ( SELECT apply_no AS cust_request_no ,row_number() OVER(PARTITION BY apply_no ORDER BY retrial_tm DESC ) rn FROM dwd_rt_pre_eos_retrial_info WHERE  retrial_typ='1'  AND ds='${TXDATE}' ) WHERE rn='1' )    --DatawareHouse_Pro.ods_ccm_credit_result_info_app     --231115新增 用于\"是否重审\"\n" +
                "  \n" +
                "    ,t1 AS ( SELECT cust_request_no , approval_result_first , approval_result_second , row_number() OVER(PARTITION BY cust_request_no ORDER BY teller_date DESC ) rn FROM ( SELECT cust_request_no , teller_date , approval_result_first , approval_result_second , row_number() OVER(PARTITION BY cust_request_no ORDER BY teller_date DESC ) rn FROM DatawareHouse_Pro.ods_ccm_credit_process_info_table WHERE approval_status = '998' AND new_sys_flag='0' AND ds='${TXDATE}' AND (TASK_CODE = 'LS_DQ_LSDK_XDJLSP' OR TASK_CODE = 'LS_DQ_LSDK_GJJLSP' OR TASK_CODE = 'LS_DQ_LSDK_XDZLSP' OR TASK_CODE = 'LS_DQ_LSDK_ZDSP') ) WHERE rn=1 UNION ALL SELECT cust_request_no , approval_result_first , approval_result_second , row_number() OVER(PARTITION BY cust_request_no ORDER BY teller_date DESC ) rn FROM ( SELECT cust_request_no , teller_date ,CASE WHEN task_code='LS_DQ_LSDK_ZDSP' THEN CASE WHEN approval_result_first IS NULL AND approval_result_second IS NULL THEN '系统拒绝' END ELSE approval_result_first END approval_result_first ,CASE WHEN task_code='LS_DQ_LSDK_ZDSP' THEN CASE WHEN approval_result_first IS NULL AND approval_result_second IS NULL THEN '系统拒绝' END ELSE approval_result_second END approval_result_second , row_number() OVER(PARTITION BY cust_request_no ORDER BY teller_date DESC ) rn FROM DatawareHouse_Pro.ods_ccm_credit_process_info_table WHERE approve = '2' AND new_sys_flag='1' AND ds='${TXDATE}' AND (TASK_CODE = 'LS_DQ_LSDK_XDJLSP' OR TASK_CODE = 'LS_DQ_LSDK_GJJLSP' OR TASK_CODE = 'LS_DQ_LSDK_XDZLSP' OR TASK_CODE = 'LS_DQ_LSDK_ZDSP') ) WHERE rn=1 )    --一级拒绝原因    --二级拒绝原因    --一级拒绝原因    --二级拒绝原因\n" +
                "\n" +
                "    ,rec AS ( SELECT cri.CUST_REQUEST_NO AS CUST_REQUEST_NO , wt2.wc AS s_wt FROM ( SELECT * FROM datawarehouse_pro.ods_ccm_credit_result_info WHERE ds='${TXDATE}' ) cri LEFT JOIN ( SELECT wt.process_id , COUNT (wt.process_id ) AS wc FROM stg_wf_taskflow_union wt WHERE wt.ds='${TXDATE}' AND wt.task_code IN ('LS_DQ_LSDK_XDZLSP', 'LS_DQ_LSDK_XDJLSP', 'LS_DQ_LSDK_GJJLSP') AND wt.wf_desc LIKE '%合作商辅助%' GROUP BY process_id ) wt2 ON cri.Process_Inst_Id = wt2.process_id )\n" +
                "    ,F_RETL_LOAN_ASSIST_TMP AS ( SELECT LVI.apply_no AS CUST_REQUEST_NO ,OP.ORDER_ID ,LVI.vin AS FRAME_NUMBER ,LVI.apply_dt AS APPLY_DATE ,OP.LOAN_ISSUE_DATE ,cri.loan_sts AS APPROVAL_STATUS ,LAI.LN_ACCTNO_STS ,ROW_NUMBER() OVER(PARTITION BY lvi.vin ORDER BY lvi.apply_dt DESC) AS RN FROM datawarehouse_pro.dwd_rt_loan_apply_vhcl_info lvi LEFT JOIN datawarehouse_pro.dwd_rt_pre_eos2_apply_dtl cri ON lvi.apply_no=cri.apply_no LEFT JOIN datawarehouse_pro.dwd_order_process_total op ON lvi.apply_no=op.cust_request_no LEFT JOIN datawarehouse_pro.dwd_ln_acct_info lai ON op.order_id=lai.ln_cod WHERE lvi.apply_dt<to_date('${TXDATE}','yyyymmdd') AND CRI.loan_sts!='000' AND CRI.loan_sts!='991' AND SUBSTR(lvi.apply_no,1,3)='020' AND lvi.vin IS NOT NULL )\n" +
                "    ,batchCarTmp AS ( SELECT t1.apply_no AS CUST_REQUEST_NO ,t1.multi_vhcl_batch_no AS car_batch_no ,t.REMARK3 FROM datawarehouse_pro.dwd_rt_pre_eos1_apply_apov_dtl t1 INNER JOIN datawarehouse_pro.stg_credit_result_info_union t ON t.CUST_REQUEST_NO = t1.apply_no AND t.ds='${TXDATE}' WHERE t1.ds='${TXDATE}' AND t.CUST_TYPE = '1' AND t.REMARK3 = 'YES' )    --加急订单\n");
        sql.append("" +
                "SELECT  DISTINCT main.cust_request_no    --\t申请编号\n" +
                "        ,main.loan_due_no    --\t借据号\n" +
                "        ,main.cust_id    --\t客户号\n" +
                "        ,main.dealer_no    --\t经销商编号\n" +
                "        ,main.dealer_name    --\t经销商名称\n" +
                "        ,main.area_name_dealer    --\t大区\n" +
                "        ,main.apply_time    --\t申请时间\n" +
                "        ,main.loan_issue_date    --\t放款时间\n" +
                "        --,NULL AS is_hist_cust--是否历史客户\n" +
                "        ,main.seller_name    -- 销售方名称\n" +
                "        ,main.car_city_code    -- 销售方城市\n" +
                "        ,main.car_province_code    -- 销售方省份\n" +
                "        ,main.drawer_name    -- 开票方名称\n" +
                "        ,decode(dapply.iou_no,NULL,'否','是') disposal_flag\n" +
                "        ,dapply.entry_amount    --车辆处置金额\n" +
                "        ,cre.manual_reason    --自动审批转人工原因\n" +
                "        ,'${TXDATE}' AS data_dt    --数据日期\n" +
                "        ,cre.teller_date    --自动审批时间\n" +
                "        ,t03lvi.down_payment_ratio_total    --23.11.1新增  首付比例\n" +
                "        ,t03lvi.vehicle_brand_name    --23.11.1新增  品牌\n" +
                "        ,t03lvi.receiver_person    --23.11.1新增  收单人员\n" +
                "        ,t03lvi.apply_interest_rate    --23.11.1新增  产品利率           \n" +
                "        ,t03lvi.interest_amount_total    --23.11.1新增  总利息金额         \n" +
                "        ,t03lvi.cust_assume_interest_amount    --23.11.1新增  客户承担利息金额   \n" +
                "        ,t03lvi.final_payment_amount    --23.11.1新增  百龙尾款金额（元） \n" +
                "        ,t03lvi.final_payment_ratio    --23.11.1新增  百龙尾款比例       \n" +
                "        ,t03lvi.down_monthly_payment    --23.11.1新增  首期月还款金额     \n" +
                "        ,t03lvi.is_collateral_loan    --23.11.1新增  是否附加品         \n" +
                "        --     ,t03lvi.is_ec_policy    --23.11.1新增  安心贷政策      \n" +
                "        ,DECODE(\n" +
                "            t03lvi.is_ec_policy\n" +
                "            ,'01'\n" +
                "            ,'无'\n" +
                "            ,'02'\n" +
                "            ,'安心贷'\n" +
                "            ,'无'\n" +
                "        )    --23.11.1新增  安心贷政策      \n" +
                "        ,t03lvi.down_payment_amount    --23.11.1新增  车辆首付金额       \n" +
                "        ,t03lvi.dealer_discount_amount    --23.11.1新增  经销商贴息         \n" +
                "        ,t03lvi.manufactor_discount_amount    --23.11.1新增  销售公司贴息               \n" +
                "        ,NVL2(dpcdl.dim_code_nm,dpcdl.dim_code_nm,'未知') AS credit_policy    ---贷款政策                                         --23.11.1新增  贷款政策           \n" +
                "        --    ,t03lvi.policy_subdivision    --23.11.1新增  政策细分 \n" +
                "        ,CASE    WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = '0' THEN '无'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = '1' THEN '无'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = '2' THEN 'B普通院校/房产房贷/普通社保'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = '01' THEN 'A1重点院校'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = '02' THEN 'A2职业职称'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = '03' OR REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = '3' THEN 'A3编制工作'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = '04' OR REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = '4' THEN 'A4高基数社保'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = '5' THEN 'A5历史客户'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = '0010' THEN '农宝宝'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = 'B1' THEN 'B1已婚AB照'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = 'A6' THEN '0A6'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = 'A7' THEN '0A7'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = 'A8' THEN '0A8'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = '0013' THEN '无'\n" +
                "                 WHEN REPLACE (REPLACE(t03lvi.policy_subdivision,'A-',''),'B-','') = 'B2' THEN 'B2已婚危险品校车' \n" +
                "                 ELSE '未匹配' \n" +
                "         END    --23.11.1新增  政策细分 \n" +
                "        ,t03lvi.total_mass    --23.11.1新增  重量           \n" +
                "        --   ,t03lvi.registration_province    --23.11.1新增  上牌省份  \n" +
                "        ,dpzrp.provice_name    --23.11.1新增  上牌省份\n" +
                "        -- ,t03lvi.registration_city    --23.11.1新增  上牌城市    \n" +
                "        ,dpzrc.city_name    --23.11.1新增  上牌城市           \n" +
                "        ,t03lvi.automotive_styling    --23.11.1新增  汽车款式           \n" +
                "        ,CASE    WHEN t03lvi.is_mortgage = 'Y' THEN '是'\n" +
                "                 WHEN t03lvi.is_mortgage = 'N' THEN '否' \n" +
                "         END AS IS_MORTGAGE    --23.11.1新增  是否免抵押\n" +
                "        ,CASE    WHEN rlcd.LOAN_MODEL_NO = '01' THEN '抵押后放款'\n" +
                "                 WHEN rlcd.LOAN_MODEL_NO = '02' THEN '上牌后放款'\n" +
                "                 WHEN rlcd.LOAN_MODEL_NO = '03' THEN '开票后放款'\n" +
                "                 WHEN rlcd.LOAN_MODEL_NO = '04' THEN '审批后放款' \n" +
                "         END AS type_drawdown    --231225  放款模式\n" +
                "        ,t04ccl.addition_down_payment_amount    --23.11.1新增  附加品首付金额(元)\n" +
                "        ,t04ccl.addition_down_payment_ratio    --23.11.1新增  附加品首付比例(%)\n" +
                "        ,t04cria.sp_account    --23.11.1新增  子账号编码\n" +
                "        ,t04cria.sp_name    --23.11.1新增  子账号名称\n" +
                "        ,CASE    WHEN MAIN.cust_request_no=crip.cust_request_no AND MAIN.cust_request_no=oaa.new_request_no THEN '大小预审均重审'\n" +
                "                 WHEN MAIN.cust_request_no=crip.cust_request_no THEN '小预审重审通过'\n" +
                "                 WHEN MAIN.cust_request_no=oaa.new_request_no THEN '大预审重审' \n" +
                "                 ELSE NULL \n" +
                "         END AS approval_biz_type_1    --23.11.15新增 是否重审\n" +
                "        ,t1.approval_result_first    --23.11.15新增 一级拒绝原因\n" +
                "        ,t1.approval_result_second    --23.11.15新增 二级拒绝原因\n" +
                "        -- ,CASE    WHEN rlcd.LOAN_MODEL_NO = '01' THEN '抵押后放款'\n" +
                "        --          WHEN rlcd.LOAN_MODEL_NO = '02' THEN '上牌后放款'\n" +
                "        --          WHEN rlcd.LOAN_MODEL_NO = '03' THEN '开票后放款'\n" +
                "        --          WHEN rlcd.LOAN_MODEL_NO = '04' THEN '审批后放款' \n" +
                "        --  END LOAN_MODEL_NO    --231225 放款模式\n" +
                "        ,bai.sp_account AS sp_user_id    --231225 子账号user_id\n" +
                "        ,ceai.hit_rule_detail    --231225 决策引擎规则\n" +
                "        ,casi1.ai_ftf_result    --231225 AI面审结果\n" +
                "        ,casi1.overall_itlgc_call    --231225 全面智能外呼结果\n" +
                "        ,npqi.is_click_button    --231225 是否点击提额按钮\n" +
                "        ,npqi.crdt_trsctn_flow    --231225 流水增信后的额度\n" +
                "        ,CASE    WHEN cr.init_exp_crdt_gnt_quota IS NULL THEN cr.exp_crdt_gnt_quota \n" +
                "                 ELSE cr.init_exp_crdt_gnt_quota \n" +
                "         END AS model_credit_limit    --231225 模型跑出的预授信额度\n" +
                "        ,casi2.is_signal_call    --231225 是否外呼后信审电核\n" +
                "        ,hocr1.is_credit_assist    --231225 是否辅助后信审电核\n" +
                "        ,decode(\n" +
                "            NVL(rec.s_wt,'0')\n" +
                "            ,'0'\n" +
                "            ,'否'\n" +
                "            ,'是'\n" +
                "        ) AS is_dealer_axlr    --231225 是否真实合作商辅助\n" +
                "        ,crictr.stream_load3    --240221         三方流水上传节点\n" +
                "        ,CASE    WHEN to_char(main.apply_time,'yyyy-mm-dd')>='2024-04-20' AND t5.if_legal_person_loan='Y' THEN '是'\n" +
                "                 WHEN to_char(main.apply_time,'yyyy-mm-dd')< '2024-04-20' AND to_char(main.apply_time,'yyyy-mm-dd')>='2023-10-01' AND t4.cust_no IS NOT NULL THEN '是' \n" +
                "                 ELSE '否' \n" +
                "         END AS corporate_loan_flag    --是否法人贷\n" +
                "        ,main.cust_name AS cust_name    --客户姓名\n" +
                "        ,dpzr.city_name AS gps_install_city    --GPS安装城市 20240709新增\n" +
                "        ,dpzr.provice_name AS gps_install_province    --GPS安装省份 20240709新增\n" +
                "        ,CASE    WHEN t03lvi.farmers ='Y' THEN '是'\n" +
                "                 WHEN t03lvi.farmers ='N' THEN '否' \n" +
                "                 ELSE NULL \n" +
                "         END AS farmers    --是否农户       20240715新增\n" +
                "        ,nvl(t03lvi.ec_apply_amount,0) AS ec_apply_amount    --安心贷申请金额  20240715新增\n" +
                "        ,CASE    WHEN t03lvi.driver_relationship='1' THEN '是' \n" +
                "                 ELSE '否' \n" +
                "         END AS driver_relationship    --驾照是否本人    20240715新增\n" +
                "        ,t03lvi.registration_city_price AS registration_city_price    --上牌城市市场价  20240715新增\n" +
                "        --\t,vie.rece_tm AS guart_dt -- 担保日期 \n" +
                "        ,CASE    WHEN substr(vie.rece_tm, -2, 2) = 'PM' THEN from_unixtime(unix_timestamp(vie.rece_tm,'dd-MMM-yy HH.mm.ss.SSSSSSSSS')+43200,'yyyy-MM-dd HH:mm:ss') \n" +
                "                 ELSE from_unixtime(unix_timestamp(vie.rece_tm,'dd-MMM-yy HH.mm.ss.SSSSSSSSS a'),'yyyy-MM-dd HH:mm:ss') \n" +
                "         END AS guart_dt    -- 担保日期 \t   \n" +
                "        ,pafc.category_name AS af_typ    --欺诈类型           \n" +
                "        ,pafc.warn_date AS af_discovery_tm\n" +
                "        ,CASE    WHEN ccri.loan_sts = '998' THEN cpits.oper_dt \n" +
                "                 ELSE NULL \n" +
                "         END AS refu_deal_dt    --拒绝处理日期 \n" +
                "        ,CASE    WHEN ccri.appr_rslt LIKE '%建议通过%' THEN NULL \n" +
                "                 ELSE ccri.appr_rslt \n" +
                "         END AS rept_refu_reas_desc    --报告中拒绝原因描\n" +
                "        ,nvl2(op.distribute_name,op.distribute_name, '系统') AS mkln_per    -- 放款人\n" +
                "        ,CASE    WHEN drlavi.wt_mode = '0' THEN '当地合作'\n" +
                "                 WHEN drlavi.wt_mode = '1' THEN '异地调车' \n" +
                "         END AS wt_mode    --合作模式\n" +
                "        ,casi2.home_visit_mana_nm AS home_visit_mana_nm    --家访经理姓名 \n" +
                "        --\t,t5.mrtg_free_typ_cd AS mrtg_free_typ --免抵押种类\n" +
                "        ,CASE    WHEN drlavi.mrtg_free_typ_cd = '1' THEN '无'\n" +
                "                 WHEN drlavi.mrtg_free_typ_cd = '2' THEN '社保类'\n" +
                "                 WHEN drlavi.mrtg_free_typ_cd = '3' THEN '房产类'\n" +
                "                 WHEN drlavi.mrtg_free_typ_cd = '4' THEN '新能源指标'\n" +
                "                 WHEN drlavi.mrtg_free_typ_cd = '5' THEN '不符合'\n" +
                "                 WHEN drlavi.mrtg_free_typ_cd = '6' THEN 'ICAR贷款申请'\n" +
                "                 WHEN drlavi.mrtg_free_typ_cd = '7' THEN '燃油指标'\n" +
                "                 WHEN drlavi.mrtg_free_typ_cd = '8' THEN '中高端新能源乘用车优质客户'\n" +
                "                 WHEN drlavi.mrtg_free_typ_cd = '10' THEN '天津网约车差异化'\n" +
                "                 WHEN drlavi.mrtg_free_typ_cd = '15' THEN '奇瑞新能源免抵押'\n" +
                "                 WHEN drlavi.mrtg_free_typ_cd = '16' THEN '新车农用运输车免抵押' \n" +
                "                 ELSE NULL \n" +
                "         END AS mrtg_free_typ    --免抵押种类\n" +
                "        --\t,cpits.apov_biz_typ  AS apov_biz_typ --是否改车价\n" +
                "        -- ,CASE    WHEN cpits.apov_biz_typ='4' THEN '是' \n" +
                "        --          ELSE '否' \n" +
                "        --  END AS apov_biz_typ\n" +
                "        ,decode(drtsi.last_apov_biz_type, '1', '新申请', '2', '重审', '3', '补资料', '4', '改车价', '5','自动审批人工','6','放款密料补传','7','合作商辅助') as apov_biz_typ\n" +
                "        --\t,t5.ahrn_mode AS ahrn_mode --授权模式\n" +
                "        ,CASE    WHEN drlavi.ahrn_mode = '0' THEN '竞争备案模式'\n" +
                "                 WHEN drlavi.ahrn_mode = '1' THEN '开放备案模式' \n" +
                "         END AS ahrn_mode    --授权模式\n" +
                "        ,NVL2(drlavi.ec_apply_amt,drlavi.ec_apply_amt,0) AS ec_apply_amt    --安心贷服务包申请价格\n" +
                "        ,drci.purchase_tax AS purchs_tax    --购置税\n" +
                "        ,drci.insure_amt AS insure_tax    --保险税 \n" +
                "        ,drci.other_add_amt AS other_add_amt    --其他附加品金额\n" +
                "        ,tdcri.return_times AS return_times    -- 退回次数\n" +
                "        ,tdcri.return_times1 AS return_times_not_phocon    -- 退回原因非”电话未接通“退回次数\n" +
                "        ,CASE    WHEN drlavi.vhcl_body_catg_nm IN ('重卡', '中卡', '挂车', '轻卡') AND drlavi.total_quality>18000 THEN '是' \n" +
                "                 ELSE '否' \n" +
                "         END AS if_heatruck    -- 是否重卡\n" +
                "        ,sn.incoming_prov_name AS entry2_prov_nm    --二网省份\n" +
                "        ,sn.incoming_city_name AS entry2_city_nm    --二网城市\n" +
                "        ,sn.incoming_area_name AS entry2_area_nm    --二网区县\n" +
                "        ,dafmc.enum_code AS cust_sign_typ    -- 客户标记类型\n" +
                "        ,decode(\n" +
                "            npqi.incre_type\n" +
                "            ,'01'\n" +
                "            ,'银行流水提额'\n" +
                "            ,'02'\n" +
                "            ,'公积金提额'\n" +
                "        ) AS incre_quota_typ    --提额类型（公积金/流水）\n" +
                "        ,CASE    WHEN dvh.create_type='1' AND drlavi.auto_apov_sts IN('3','4') THEN '是' \n" +
                "                 ELSE '否' \n" +
                "         END if_add_doc_auto_apov\n" +
                "        ,CASE    WHEN dvh.create_type='2' AND drlavi.auto_apov_sts IN('3','4') THEN '是' \n" +
                "                 ELSE '否' \n" +
                "         END if_car_mod_price_auto_apov\n" +
                "        ,CASE    WHEN dvhc.approve_way='1' AND afrlsb.APPROVAL_WAY='人工审批' THEN '人工审批-人工审批'\n" +
                "                 WHEN dvhc.num=1 AND (afrlsb.APPROVAL_WAY='自动审批' AND afrlsb.IS_AI='是') OR afrlsb.IS_AUTO_CALL='是' THEN CONCAT(CONCAT(DECODE(dvhc.approve_way,'1','人工审批','2','自动审批'),'-'),'智能审批')\n" +
                "                 WHEN dvhc.num>1 AND afrlsb.APPROVAL_WAY='自动审批' AND NVL(afrlsb.IS_AI,'否')='否' AND NVL(afrlsb.IS_AUTO_CALL,'否')='否' THEN CONCAT(CONCAT(DECODE(dvhc.approve_way,'1','人工审批','2','自动审批'),'-'),'模型审批') \n" +
                "         END subdi_apov_way\n" +
                "        ,cr.multi_vhcl_batch_no\n" +
                "        ,drlavi.addinst_vhcl_typ_nm AS vhcl_biz_typ\n" +
                "        ,drd.install_province_name AS gps_inst_provn\n" +
                "        ,CASE    WHEN drv.location_address IS NOT NULL THEN SUBSTR(drv.location_address,1, regexp_instr(REPLACE(drv.location_address,'省道',''), '(?<=省)|(?<=自治区)|(?<=特别行政区)|(?<=上海市)|(?<=天津市)|(?<=北京市)|(?<=重庆市)',1,1)-1) \n" +
                "                 ELSE NULL \n" +
                "         END AS gps_offline_loca_provn\n" +
                "        ,(drv.mileage/1000) AS gps_km\n" +
                "        ,CASE    WHEN drv.device_status='0' THEN '静止'\n" +
                "                 WHEN drv.device_status='1' THEN '行驶'\n" +
                "                 WHEN drv.device_status='10' THEN '离线' \n" +
                "                 ELSE NULL \n" +
                "         END AS gps_device_sts\n" +
                "        ,decode(\n" +
                "            sn.filings_status\n" +
                "            ,'0'\n" +
                "            ,'有效'\n" +
                "            ,'1'\n" +
                "            ,'无效'\n" +
                "            ,'2'\n" +
                "            ,'清退'\n" +
                "            ,'3'\n" +
                "            ,'在途'\n" +
                "            ,'4'\n" +
                "            ,'撤销'\n" +
                "            ,'5'\n" +
                "            ,'拒绝'\n" +
                "        ) AS entry2_record_sts\n" +
                "        ,CASE    WHEN docr.num IS NOT NULL AND docr.num>0 THEN '是' \n" +
                "                 ELSE '否' \n" +
                "         END AS if_pre_eos1_intllgnt_outbnd\n" +
                "        ,CASE    WHEN scriu.REMARK3='YES' OR batchCar.if_urgnt_flag='Y' THEN '是' \n" +
                "                 ELSE '否' \n" +
                "         END\n" +
                "        --   ,decode(drlavi.enprise_ahrn_typ,'1','电子','2','纸质')\n" +
                "        ,CASE    WHEN drlavi.enprise_ahrn_typ='2' AND drlavi.enprise_credit_query_sts IS NULL THEN NULL\n" +
                "                 WHEN drlavi.enprise_ahrn_typ='1' THEN '电子'\n" +
                "                 WHEN drlavi.enprise_ahrn_typ='2' THEN '纸质' \n" +
                "                 ELSE NULL \n" +
                "         END enprise_credit_ahrn_typ\n" +
                "        ,decode(\n" +
                "            drlavi.enprise_credit_query_sts\n" +
                "            ,'0'\n" +
                "            ,'查询中'\n" +
                "            ,'1'\n" +
                "            ,'查询成功'\n" +
                "            ,'-1'\n" +
                "            ,'查询失败'\n" +
                "        )\n" +
                "        ,SPLIT_PART(t1.approval_result_first,',',1) AS rslt_frs\n" +
                "        ,CASE    WHEN regexp_count(t1.approval_result_first,',')>0 THEN extractvalue(t1.approval_result_second,substr(SPLIT_PART(t1.approval_result_first,',',1),1,regexp_instr(SPLIT_PART(t1.approval_result_first,',',1),'.',1,1))) \n" +
                "                 ELSE t1.approval_result_second \n" +
                "         END AS rslt_scn\n" +
                "        --     ,un.BUSS_TYPE\n" +
                "        --    ,un.BUSS_TYPE_name \n" +
                "        --    ,linfo.cert_if\n" +
                "        --     ,linfo.mobile_if\n" +
                "        --     ,linfo.cert_no_exist_typ\n" +
                "        --  ,null,null,null\n" +
                "        ,main.enterprise_subject\n" +
                "        ,main.order_source\n" +
                "        ,main.product_subject\n" +
                "FROM    main\n" +
                "LEFT JOIN DatawareHouse_Pro.dws_t03_retl_loan_info cre\n" +
                "ON      main.cust_request_no = cre.cust_request_no\n" +
                "AND     cre.ds = '${TXDATE}' LEFT\n" +
                "JOIN    DatawareHouse_Pro.dws_t01_app_pre_approval_info capp\n" +
                "ON      main.cust_request_no = capp.cust_request_no\n" +
                "AND     capp.ds = '${TXDATE}'\n" +
                "LEFT JOIN DatawareHouse_Pro.dws_t03_loan_application_info linfo\n" +
                "ON      main.cust_request_no = linfo.cust_request_no\n" +
                "AND     linfo.ds = '${TXDATE}' LEFT\n" +
                "JOIN    DatawareHouse_Pro.dws_t03_loan_vehicle_info t03lvi\n" +
                "ON      t03lvi.cust_request_no = main.cust_request_no\n" +
                "AND     t03lvi.ds = '${TXDATE}'\n" +
                "LEFT JOIN DatawareHouse_Pro.dws_t01_heavy_truck_relation htr2\n" +
                "ON      main.cust_request_no = htr2.trailer_request_no\n" +
                "AND     htr2.ds = '${TXDATE}' LEFT\n" +
                "JOIN    (\n" +
                "            SELECT  cust_request_no\n" +
                "                    ,entry_amount\n" +
                "                    ,iou_no\n" +
                "                    ,ROW_NUMBER() OVER(PARTITION BY cust_request_no ORDER BY createddate DESC) rn\n" +
                "            FROM    DatawareHouse_Pro.dws_t01_disposal_apply\n" +
                "            WHERE   ds = '${TXDATE}'\n" +
                "        ) dapply\n" +
                "ON      main.cust_request_no = dapply.cust_request_no\n" +
                "AND     dapply.rn = '1'\n" +
                "LEFT JOIN dws_t04_credit_collateral_loan t04ccl\n" +
                "ON      main.cust_request_no = t04ccl.cust_request_no\n" +
                "---导致数据重复\n" +
                "AND     t04ccl.ds = '${TXDATE}' LEFT\n" +
                "JOIN    dws_t04_credit_result_info_app T04CRIA\n" +
                "ON      main.cust_request_no = t04cria.cust_request_no\n" +
                "AND     T04CRIA.ds = '${TXDATE}'\n" +
                "LEFT JOIN oaa\n" +
                "ON      main.cust_request_no = oaa.new_request_no LEFT    --231115新增  用于\"是否重审\"\n" +
                "JOIN    crip\n" +
                "ON      main.cust_request_no = crip.cust_request_no    --231115新增  用于\"是否重审\"\n" +
                "LEFT JOIN t1\n" +
                "ON      main.cust_request_no = t1.cust_request_no LEFT    --231115新增  用于一级拒绝原因、二级拒绝原因\n" +
                "JOIN    dim_public_cd_tbl dpcdl\n" +
                "ON      trim(dpcdl.dim_code_no) = T03LVI.credit_policy\n" +
                "AND     dpcdl.idx_no = 'CCM003'\n" +
                "----后续新增字段\n" +
                "LEFT JOIN ods_clm_retail_loan_contract_data rlcd\n" +
                "ON      rlcd.REQUEST_NO = MAIN.CUST_REQUEST_NO\n" +
                "AND     rlcd.ds = '${TXDATE}' LEFT    --231225 放款模式\n" +
                "JOIN    stg_b_apply_loan_info bai\n" +
                "ON      main.application_id = bai.application_id\n" +
                "AND     bai.ds = '${TXDATE}'    --231225 子账号user_id\n" +
                "LEFT JOIN (\n" +
                "              SELECT  t1.cust_request_no    --table\n" +
                "                      ,t1.hit_rule_detail    --决策引擎规则/\n" +
                "              FROM    (\n" +
                "                          SELECT  t.cust_request_no\n" +
                "                                  ,t.hit_rule_detail    --决策引擎规则\n" +
                "                                  ,row_number() OVER(PARTITION BY t.cust_request_no ORDER BY t.created_stamp DESC) rn\n" +
                "                          FROM    DatawareHouse_Pro.ods_ccm_credit_eos_approve_info t\n" +
                "                          WHERE   t.ds = '${TXDATE}'\n" +
                "                          AND     (\n" +
                "                                          t.eos_score IS NOT NULL\n" +
                "                                      OR  t.risk_level IS NOT NULL\n" +
                "                                      OR  t.hit_rule_detail IS NOT NULL\n" +
                "                                  )\n" +
                "                      ) t1\n" +
                "              WHERE   rn = 1\n" +
                "              AND     t1.hit_rule_detail IS NOT NULL\n" +
                "          ) ceai\n" +
                "ON      ceai.cust_request_no = main.cust_request_no LEFT    --231225 决策引擎规则\n" +
                "JOIN    (\n" +
                "            SELECT  a.apply_no AS cust_request_no\n" +
                "                    ,CASE    WHEN a.ai_ffrew_idn = 1 THEN 'AI面审中'\n" +
                "                             WHEN a.ai_ffrew_idn = 2 THEN 'AI面审转人工'\n" +
                "                             WHEN a.ai_ffrew_idn = 3 THEN 'AI面审通过'\n" +
                "                             WHEN a.ai_ffrew_idn = 4 THEN '拒绝' \n" +
                "                     END AS ai_ftf_result    --231225 AI面审结果\n" +
                "                    ,CASE    WHEN a.intllgnt_outbnd_idn = 1 THEN '外呼中'\n" +
                "                             WHEN a.intllgnt_outbnd_idn = 2 THEN '转人工'\n" +
                "                             WHEN a.intllgnt_outbnd_idn = 3 THEN '退回经销商'\n" +
                "                             WHEN a.intllgnt_outbnd_idn = 4 THEN '通过'\n" +
                "                             WHEN a.intllgnt_outbnd_idn = 5 THEN '拒绝' \n" +
                "                     END AS overall_itlgc_call    --231225 全面智能外呼结果\n" +
                "            FROM    DatawareHouse_Pro.dwd_rt_pre_eos2_apov_dtl a\n" +
                "            WHERE   a.apov_idn IN ('AI','AUTO-CALL')\n" +
                "            AND     a.ds = '${TXDATE}'\n" +
                "        ) casi1    --231225 AI面审和全面智能外呼\n" +
                "ON      main.cust_request_no = casi1.cust_request_no\n" +
                "LEFT JOIN (\n" +
                "              SELECT  iqi1.cust_request_no\n" +
                "                      ,CASE    WHEN iqi1.apply_status = '1' THEN '已提醒用户上传流水'\n" +
                "                               WHEN iqi1.apply_status = '2' THEN '用户已提额'\n" +
                "                               WHEN iqi1.apply_status = '3' THEN '提额失败' \n" +
                "                               ELSE NULL \n" +
                "                       END AS is_click_button    --231225 是否点击提额按钮\n" +
                "                      ,iqi1.new_pre_approval_quota AS crdt_trsctn_flow    --231225 流水增信后的额度\n" +
                "                      ,iqi1.incre_type\n" +
                "              FROM    (\n" +
                "                          SELECT  ROW_NUMBER() OVER(PARTITION BY iqi.CUST_REQUEST_NO ORDER BY LAST_UPDATED_STAMP DESC) rn\n" +
                "                                  ,iqi.cust_request_no\n" +
                "                                  ,iqi.apply_status\n" +
                "                                  ,iqi.new_pre_approval_quota\n" +
                "                                  ,iqi.incre_type\n" +
                "                          FROM    stg_increase_quota_info iqi\n" +
                "                          WHERE   iqi.ds = '${TXDATE}'\n" +
                "                      ) iqi1\n" +
                "              WHERE   rn = 1\n" +
                "          ) npqi\n" +
                "--数据6055707\n" +
                "ON      main.cust_request_no = npqi.cust_request_no LEFT\n" +
                "JOIN    (\n" +
                "            SELECT  DISTINCT apply_no\n" +
                "                    ,init_exp_crdt_gnt_quota\n" +
                "                    ,exp_crdt_gnt_quota\n" +
                "                    ,multi_vhcl_batch_no\n" +
                "            FROM    DatawareHouse_Pro.dwd_rt_pre_eos1_apply_apov_dtl\n" +
                "            WHERE   ds = '${TXDATE}'\n" +
                "        ) cr\n" +
                "ON      cr.apply_no = main.cust_request_no    --231225 模型跑出的预授信额度\n" +
                "LEFT JOIN (\n" +
                "              SELECT  b.cust_request_no\n" +
                "                      ,a.home_visit_mana_nm\n" +
                "                      ,CASE    WHEN a.apov_idn = 'AUTO-CALL' AND b.c_crn > 0 THEN '是' \n" +
                "                               ELSE '否' \n" +
                "                       END is_signal_call    --231225 是否外呼后信审电核\n" +
                "              FROM    DatawareHouse_Pro.dwd_rt_pre_eos2_apov_dtl a\n" +
                "              LEFT JOIN (\n" +
                "                            SELECT  cust_request_no\n" +
                "                                    ,COUNT(c_crn) AS c_crn\n" +
                "                            FROM    (\n" +
                "                                        SELECT  cm.cust_request_no\n" +
                "                                                ,COUNT(cm.cust_request_no) c_crn\n" +
                "                                        FROM    DatawareHouse_Pro.ods_ccm_hand_outbound_call_result_di ho INNER    ---904168\n" +
                "                                        JOIN    DatawareHouse_Pro.ods_ccm_credit_mobile_info cm    --\n" +
                "                                        ON      ho.caller = cm.mobile\n" +
                "                                        AND     cm.ds = '${TXDATE}'\n" +
                "                                        WHERE   ho.ds <= '${TXDATE}'\n" +
                "                                        AND     ho.source_type = 'X'\n" +
                "                                        AND     ho.duration > '0'\n" +
                "                                        AND     cm.person_type = '1'\n" +
                "                                        GROUP BY cm.cust_request_no\n" +
                "                                        UNION ALL\n" +
                "                                        SELECT  cm.cust_request_no\n" +
                "                                                ,COUNT(cm.cust_request_no) c_crn\n" +
                "                                        FROM    DatawareHouse_Pro.ods_ccm_hand_outbound_call_result_di ho INNER    ---904168\n" +
                "                                        JOIN    DatawareHouse_Pro.ods_ccm_credit_mobile_info cm    --\n" +
                "                                        ON      ho.called = cm.mobile\n" +
                "                                        AND     cm.ds = '${TXDATE}'\n" +
                "                                        WHERE   ho.ds <= '${TXDATE}'\n" +
                "                                        AND     ho.source_type = 'X'\n" +
                "                                        AND     ho.duration > '0'\n" +
                "                                        AND     cm.person_type = '1'\n" +
                "                                        GROUP BY cm.cust_request_no\n" +
                "                                    ) \n" +
                "                            GROUP BY cust_request_no\n" +
                "                        ) b\n" +
                "              ON      a.apply_no = b.cust_request_no\n" +
                "              WHERE   a.ds = '${TXDATE}'\n" +
                "          ) casi2\n" +
                "ON      main.cust_request_no = casi2.cust_request_no LEFT    --231225 是否外呼后信审电核\n" +
                "JOIN    (\n" +
                "            SELECT  rec.cust_request_no\n" +
                "                    ,CASE    WHEN nvl(rec.s_wt,0) <> 0 AND b1.c_crn > 0 THEN '是' \n" +
                "                             ELSE '否' \n" +
                "                     END AS is_credit_assist    --是否辅助后信审电核        \n" +
                "            FROM    rec\n" +
                "            LEFT JOIN (\n" +
                "                          SELECT  cust_request_no\n" +
                "                                  ,COUNT(c_crn) AS c_crn\n" +
                "                          FROM    (\n" +
                "                                      SELECT  cm.cust_request_no\n" +
                "                                              ,COUNT(cm.cust_request_no) c_crn\n" +
                "                                      FROM    DatawareHouse_Pro.ods_ccm_hand_outbound_call_result_di ho INNER    ---904168\n" +
                "                                      JOIN    DatawareHouse_Pro.ods_ccm_credit_mobile_info cm    --\n" +
                "                                      ON      ho.caller = cm.mobile\n" +
                "                                      AND     cm.ds = '${TXDATE}'\n" +
                "                                      WHERE   ho.ds <= '${TXDATE}'\n" +
                "                                      AND     ho.source_type = 'X'\n" +
                "                                      AND     ho.duration > '0'\n" +
                "                                      AND     cm.person_type = '1'\n" +
                "                                      GROUP BY cm.cust_request_no\n" +
                "                                      UNION ALL\n" +
                "                                      SELECT  cm.cust_request_no\n" +
                "                                              ,COUNT(cm.cust_request_no) c_crn\n" +
                "                                      FROM    DatawareHouse_Pro.ods_ccm_hand_outbound_call_result_di ho INNER    ---904168\n" +
                "                                      JOIN    DatawareHouse_Pro.ods_ccm_credit_mobile_info cm    --\n" +
                "                                      ON      ho.called = cm.mobile\n" +
                "                                      AND     cm.ds = '${TXDATE}'\n" +
                "                                      WHERE   ho.ds <= '${TXDATE}'\n" +
                "                                      AND     ho.source_type = 'X'\n" +
                "                                      AND     ho.duration > '0'\n" +
                "                                      AND     cm.person_type = '1'\n" +
                "                                      GROUP BY cm.cust_request_no\n" +
                "                                  ) \n" +
                "                          GROUP BY cust_request_no\n" +
                "                      ) b1\n" +
                "            ON      rec.cust_request_no = b1.cust_request_no\n" +
                "        ) hocr1\n" +
                "ON      hocr1.cust_request_no = main.CUST_REQUEST_NO    --231225 是否辅助后信审电核\n" +
                "LEFT JOIN rec\n" +
                "ON      rec.cust_request_no = main.cust_request_no LEFT\n" +
                "JOIN    (\n" +
                "            SELECT  DISTINCT cri.apply_no AS cust_request_no\n" +
                "                    ,CASE    WHEN ctr.cust_request_no IS NULL THEN NULL\n" +
                "                             WHEN ctr.cust_request_no IN (SELECT cust_request_no FROM dws_t01_crdtr_return_info WHERE ds='${TXDATE}') THEN '信审退回上传'\n" +
                "                             WHEN ctr.CUST_REQUEST_NO IN (SELECT CUST_REQUEST_NO FROM DatawareHouse_Pro.ods_ccm_increase_quota_info WHERE INCRE_TYPE='01' AND ds='${TXDATE}') THEN '增信提额上传' \n" +
                "                             ELSE '作为收入类材料自主上传' \n" +
                "                     END AS stream_load3\n" +
                "            FROM    (\n" +
                "                        SELECT  DISTINCT apply_no\n" +
                "                        FROM    DatawareHouse_Pro.dwd_rt_pre_eos2_apply_dtl\n" +
                "                        WHERE   ds = '${TXDATE}'\n" +
                "                    ) CRI\n" +
                "            LEFT JOIN DatawareHouse_Pro.ods_ccm_credit_cust_trans_report ctr    ---- 240221 三方流水表  --20240614 新老合并\n" +
                "            ON      CRI.apply_no = ctr.cust_request_no\n" +
                "            AND     ctr.ds = '${TXDATE}'\n" +
                "        ) crictr\n" +
                "ON      crictr.cust_request_no = main.cust_request_no\n" +
                "LEFT JOIN datawarehouse_pro.dwd_rt_loan_apply_vhcl_info t5\n" +
                "ON      main.cust_request_no = t5.apply_no\n" +
                "AND     t5.if_legal_person_loan = 'Y'\n" +
                "AND     t5.ds = '${TXDATE}' LEFT\n" +
                "JOIN    datawarehouse_pro.ods_mpf_dmp_legal_person_cust_dtl t4\n" +
                "ON      main.cust_id = t4.cust_no\n" +
                "AND     substr(batch_id,1,8) = '20240423'\n" +
                "LEFT JOIN datawarehouse_pro.dwd_rt_ctrct_info drci\n" +
                "ON      drci.apply_no = main.cust_request_no\n" +
                "AND     drci.ds = '${TXDATE}' LEFT\n" +
                "JOIN    datawarehouse_pro.dwd_postal_zone_region dpzr\n" +
                "ON      dpzr.city_id = drci.gps_inst_city\n" +
                "AND     dpzr.ds = '${TXDATE}'\n" +
                "--上牌省市存在城市为空 省份不为空的情况\n" +
                "LEFT JOIN (\n" +
                "              SELECT  DISTINCT provice_id\n" +
                "                      ,provice_name\n" +
                "              FROM    datawarehouse_pro.dwd_postal_zone_region\n" +
                "              WHERE   ds = '${TXDATE}'\n" +
                "          ) dpzrp\n" +
                "ON      t03lvi.registration_province = dpzrp.provice_id LEFT\n" +
                "JOIN    datawarehouse_pro.dwd_postal_zone_region dpzrc\n" +
                "ON      t03lvi.registration_city = dpzrc.city_id\n" +
                "AND     dpzrc.ds = '${TXDATE}'\n" +
                "LEFT JOIN (\n" +
                "              SELECT  apply_no\n" +
                "                      ,receive_date\n" +
                "                      ,guarantee_type\n" +
                "                      ,rece_tm\n" +
                "              FROM    (\n" +
                "                          SELECT  ROW_NUMBER () OVER ( PARTITION BY T.APPLY_NO ORDER BY T.RECEIVE_DATE DESC ) RN\n" +
                "                                  ,t.apply_no\n" +
                "                                  ,t.receive_date\n" +
                "                                  ,t.guarantee_type\n" +
                "                                  ,t.rece_tm\n" +
                "                          FROM    (\n" +
                "                                      SELECT  apply_no\n" +
                "                                              ,TO_CHAR(last_upt_tm, 'YYYY-MM-DD') AS RECEIVE_DATE\n" +
                "                                              ,guart_typ AS guarantee_type\n" +
                "                                              ,rece_tm\n" +
                "                                      FROM    datawarehouse_pro.dwd_vhcl_guart_info\n" +
                "                                      WHERE   ds = '${TXDATE}'\n" +
                "                                      AND     if_guart IS NOT NULL\n" +
                "                                  ) t\n" +
                "                      ) \n" +
                "              WHERE   RN = 1\n" +
                "          ) vie\n" +
                "ON      main.CUST_REQUEST_NO = vie.apply_no LEFT    --新增担保日期\n" +
                "JOIN    (\n" +
                "            SELECT  apply_no\n" +
                "                    ,warn_date\n" +
                "                    ,category_name\n" +
                "            FROM    (\n" +
                "                        SELECT  ROW_NUMBER() OVER(PARTITION BY a.apply_no ORDER BY a.warn_date ASC) rn\n" +
                "                                ,a.apply_no\n" +
                "                                ,a.warn_date\n" +
                "                                ,b.grade_typ AS category_type\n" +
                "                                ,b.typ_nm AS category_name\n" +
                "                        FROM    datawarehouse_pro.dwd_t_p_rew_ret_af_cust a\n" +
                "                        LEFT JOIN datawarehouse_pro.dwd_af_cust_nospcfctn_typ b\n" +
                "                        ON      a.AF_TWO = b.catg_no\n" +
                "                        AND     b.ds = '${TXDATE}'\n" +
                "                        WHERE   a.ds = '${TXDATE}'\n" +
                "                        AND     b.grade_typ = '2'\n" +
                "                    ) \n" +
                "            WHERE   rn = 1\n" +
                "        ) pafc\n" +
                "ON      main.cust_request_no = pafc.apply_no    -- 欺诈类型和欺诈时间\n" +
                "LEFT JOIN (\n" +
                "              SELECT  DISTINCT apply_no\n" +
                "                      ,appr_rslt\n" +
                "                      ,loan_sts\n" +
                "              FROM    datawarehouse_pro.dwd_rt_pre_eos2_apply_dtl\n" +
                "              WHERE   ds = '${TXDATE}'\n" +
                "          ) ccri\n" +
                "ON      main.cust_request_no = ccri.apply_no LEFT    --拒绝处理日期 报告中拒绝原因描述\n" +
                "JOIN    datawarehouse_pro.dwd_rt_loan_apply_vhcl_info drlavi\n" +
                "ON      main.cust_request_no = drlavi.apply_no\n" +
                "AND     drlavi.ds = '${TXDATE}'\n" +
                "-- left join (\n" +
                "-- select m.doccode,d.BUSS_TYPE,d.BUSS_TYPE_name,ROW_NUMBER() OVER(PARTITION BY REPLACE(m.doccode,concat(d.BUSS_TYPE,'_'),'') ORDER BY m.dates desc) AS rn    from (\n" +
                "-- select doccode,busstype,NVL(update_date,create_date) as dates  from  DatawareHouse_Pro.ods_image_ES_DOC_MAIN\n" +
                "-- where ds='${TXDATE}' ) m\n" +
                "-- left join (\n" +
                "--  select BUSS_TYPE,BUSS_TYPE_name  from  DatawareHouse_Pro.ods_image_ES_DOC_DEF\n" +
                "-- where  ds='${TXDATE}'  group by  BUSS_TYPE,BUSS_TYPE_name \n" +
                "-- ) d\n" +
                "-- on m.BUSSTYPE=d.BUSS_TYPE\n" +
                "-- ) un on drlavi.img_no=REPLACE(un.doccode,concat(un.BUSS_TYPE,'_'),'') and un.rn='1'\n" +
                "LEFT JOIN datawarehouse_pro.dwd_order_process_total op\n" +
                "ON      main.cust_request_no = op.cust_request_no\n" +
                "AND     op.ds = '${TXDATE}' LEFT\n" +
                "JOIN    (\n" +
                "            SELECT  type_123.apply_no\n" +
                "                    ,CASE    WHEN type_4.apov_biz_typ = '4' THEN type_4.apov_biz_typ \n" +
                "                             ELSE type_123.apov_biz_typ \n" +
                "                     END AS apov_biz_typ\n" +
                "                    ,CASE    WHEN type_4.apov_biz_typ = '4' THEN type_4.oper_dt \n" +
                "                             ELSE type_123.oper_dt \n" +
                "                     END AS oper_dt\n" +
                "            FROM    (\n" +
                "                        SELECT  apply_no\n" +
                "                                ,apov_biz_typ\n" +
                "                                ,oper_dt\n" +
                "                        FROM    (\n" +
                "                                    SELECT  t.apply_no\n" +
                "                                            ,t.apov_biz_typ\n" +
                "                                            --是否改车价\n" +
                "                                            ,t.oper_dt\n" +
                "                                            --拒绝处理日期\n" +
                "                                            ,ROW_NUMBER() OVER(PARTITION BY t.apply_no ORDER BY t.oper_dt DESC ) rn\n" +
                "                                    FROM    datawarehouse_pro.dwd_rt_pre_eos2_apov_schdl_dtl t\n" +
                "                                    WHERE   t.ds = '${TXDATE}'\n" +
                "                                    AND     apov_biz_typ IS NOT NULL\n" +
                "                                ) \n" +
                "                        WHERE   rn = 1\n" +
                "                    ) type_123\n" +
                "            LEFT JOIN (\n" +
                "                          SELECT  apply_no\n" +
                "                                  ,apov_biz_typ\n" +
                "                                  ,oper_dt\n" +
                "                          FROM    (\n" +
                "                                      SELECT  t.apply_no\n" +
                "                                              ,t.apov_biz_typ\n" +
                "                                              --是否改车价\n" +
                "                                              ,t.oper_dt\n" +
                "                                              --拒绝处理日期\n" +
                "                                              ,ROW_NUMBER() OVER(PARTITION BY t.apply_no ORDER BY t.apov_biz_typ DESC, t.oper_dt DESC ) rn\n" +
                "                                      FROM    datawarehouse_pro.dwd_rt_pre_eos2_apov_schdl_dtl t\n" +
                "                                      WHERE   t.ds = '${TXDATE}'\n" +
                "                                      AND     apov_biz_typ IS NOT NULL\n" +
                "                                  ) \n" +
                "                          WHERE   rn = 1\n" +
                "                      ) type_4\n" +
                "            ON      type_123.apply_no = type_4.apply_no\n" +
                "        ) cpits\n" +
                "ON      main.cust_request_no = cpits.apply_no\n" +
                "LEFT JOIN (\n" +
                "              SELECT  cust_request_no\n" +
                "                      ,COUNT(cust_request_no) AS return_times\n" +
                "                      --退回次数\n" +
                "                      ,SUM(\n" +
                "                          CASE    WHEN approval_result_second NOT LIKE '%电话未接通%' THEN 1 \n" +
                "                                  ELSE 0 \n" +
                "                          END\n" +
                "                      ) AS return_times1\n" +
                "              FROM    datawarehouse_pro.dws_t01_crdtr_return_info t\n" +
                "              WHERE   ds = '${TXDATE}'    --20240722退回调整为退回明细\n" +
                "              GROUP BY cust_request_no\n" +
                "          ) tdcri\n" +
                "ON      main.cust_request_no = tdcri.cust_request_no LEFT\n" +
                "JOIN    (    --进件二网备案城市和省份\n" +
                "            --进件二网备案表\n" +
                "            SELECT  t1.partner_id    --经销商编码\n" +
                "                    ,t1.partner_name    --经销商名称\n" +
                "                    ,t1.filings_status\n" +
                "                    ,REPLACE(\n" +
                "                        REPLACE(\n" +
                "                            REPLACE(REPLACE(t1.incoming_name,' ',''),'t','')\n" +
                "                            ,chr(9)\n" +
                "                            ,''\n" +
                "                        )\n" +
                "                        ,chr(10)\n" +
                "                        ,''\n" +
                "                    ) incoming_name    --进件渠道名称\n" +
                "                    ,CASE    WHEN dsnri.province IS NOT NULL THEN dsnri.province \n" +
                "                             ELSE t1.incoming_prov_name \n" +
                "                     END AS incoming_prov_name    --进件渠道省份名称\n" +
                "                    ,CASE    WHEN dsnri.city IS NOT NULL THEN dsnri.city \n" +
                "                             ELSE t1.incoming_city_name \n" +
                "                     END AS incoming_city_name    --进件渠道城市名称\n" +
                "                    ,CASE    WHEN dsnri.district IS NOT NULL THEN dsnri.district \n" +
                "                             ELSE t1.incoming_area_name \n" +
                "                     END AS incoming_area_name    --进件渠道区县名称\n" +
                "            FROM    (\n" +
                "                        SELECT  t.filings_id\n" +
                "                                ,t.instance_id\n" +
                "                                ,t.filings_type\n" +
                "                                ,t.filings_tag\n" +
                "                                ,t.filings_tag_name\n" +
                "                                ,t.partner_id\n" +
                "                                ,t.partner_name\n" +
                "                                ,t.partner_pro_code\n" +
                "                                ,t.partner_pro_name\n" +
                "                                ,t.partner_city_code\n" +
                "                                ,t.partner_city_name\n" +
                "                                ,t.partner_region_code\n" +
                "                                ,t.partner_region_name\n" +
                "                                ,t.apply_date\n" +
                "                                ,t.start_date\n" +
                "                                ,t.end_date\n" +
                "                                ,t.filings_status\n" +
                "                                ,t.documents_mail_status\n" +
                "                                ,t.incoming_id\n" +
                "                                ,t.incoming_name\n" +
                "                                ,t.incoming_prov_code\n" +
                "                                ,t.incoming_prov_name\n" +
                "                                ,t.incoming_city_code\n" +
                "                                ,t.incoming_city_name\n" +
                "                                ,t.incoming_area_code\n" +
                "                                ,t.incoming_area_name\n" +
                "                                ,t.incoming_town_code\n" +
                "                                ,t.incoming_town_name\n" +
                "                                ,t.incoming_region_code\n" +
                "                                ,t.incoming_region_name\n" +
                "                                ,t.main_account\n" +
                "                                ,t.special_remark\n" +
                "                                ,t.image_id\n" +
                "                                ,t.unified_social_credit_code\n" +
                "                                ,t.put_on_record_properties\n" +
                "                                ,t.dot_properties\n" +
                "                                ,t.put_on_record_mode\n" +
                "                                ,t.put_on_record_count\n" +
                "                                ,t.main_brand\n" +
                "                                ,t.pay_mode\n" +
                "                                ,t.collection_account_name\n" +
                "                                ,t.collection_account\n" +
                "                                ,t.opening_bank_area\n" +
                "                                ,t.opening_bank_area_label\n" +
                "                                ,t.opening_bank_name\n" +
                "                                ,t.opening_bank\n" +
                "                                ,t.opening_bank_label\n" +
                "                                ,t.opening_bank_code\n" +
                "                                ,t.sign_type\n" +
                "                                ,t.sign_phone\n" +
                "                                ,t.remark\n" +
                "                                ,t.create_by\n" +
                "                                ,t.create_time\n" +
                "                                ,t.update_by\n" +
                "                                ,t.update_time\n" +
                "                                ,t.deleted_flag\n" +
                "                                ,t.update_status\n" +
                "                                ,t.sync_data_flag\n" +
                "                                ,t.cooperation_mode\n" +
                "                                ,t.is_seller_invoice\n" +
                "                                ,t.juridical_person_name\n" +
                "                                ,t.juridical_person_id\n" +
                "                                ,t.juridical_person_phone\n" +
                "                                ,t.actual_controller_name\n" +
                "                                ,t.actual_controller_id\n" +
                "                                ,t.actual_controller_phone\n" +
                "                                ,t.product_from\n" +
                "                                ,t.related_company_info\n" +
                "                                ,t.term_start\n" +
                "                                ,t.term_end\n" +
                "                                ,t.registe_time\n" +
                "                                ,t.regist_capi\n" +
                "                                ,t.sales_manager_exam_user_id\n" +
                "                                ,t.sales_manager_exam_user_name\n" +
                "                                ,t.auto_exam_flag\n" +
                "                                ,row_number() OVER(PARTITION BY t.partner_id,REPLACE(REPLACE(REPLACE(REPLACE(t.incoming_name,' ',''),'t',''),chr(9),''),chr(10),''),t.filings_tag ORDER BY t.update_time DESC) AS rk\n" +
                "                        FROM    DatawareHouse_Pro.dwd_sep_filings t\n" +
                "                        WHERE   t.filings_tag = 'BA_JJEWBA'\n" +
                "                        AND     t.ds = '${TXDATE}'\n" +
                "                    ) t1\n" +
                "            LEFT JOIN DatawareHouse_Pro.dwd_dmp_second_network_rsgis_info dsnri    --线下补录表\n" +
                "            ON      t1.incoming_name = dsnri.company_name\n" +
                "            AND     dsnri.ds = '${TXDATE}'\n" +
                "            WHERE   t1.rk = 1\n" +
                "        ) sn\n" +
                "ON      sn.partner_id = main.dealer_no\n" +
                "AND     REPLACE(REPLACE(REPLACE(REPLACE(main.second_network,' ',''),'t',''),chr(9),''),chr(10),'') = sn.incoming_name\n" +
                "--零售数据贯通批次2\n" +
                "-- 不开窗数据存在重复\n" +
                "LEFT JOIN (\n" +
                "              SELECT  enum_code\n" +
                "                      ,cust_request_no\n" +
                "                      ,ROW_NUMBER() OVER(PARTITION BY cust_request_no ORDER BY last_updated_stamp DESC ) AS rn\n" +
                "              FROM    DatawareHouse_Pro.dwd_apply_for_mark_customer\n" +
                "              WHERE   ds = '${TXDATE}'\n" +
                "          ) dafmc\n" +
                "ON      main.cust_request_no = dafmc.cust_request_no\n" +
                "AND     dafmc.rn = '1' LEFT\n" +
                "JOIN    (\n" +
                "            SELECT  cust_request_no\n" +
                "                    ,create_type\n" +
                "                    ,ROW_NUMBER() OVER(PARTITION BY cust_request_no ORDER BY NVL(last_updated_stamp,created_stamp) DESC ) AS rn\n" +
                "            FROM    datawarehouse_pro.dwd_loan_vehicle_info_his\n" +
                "            WHERE   ds = '${TXDATE}'\n" +
                "        ) dvh\n" +
                "ON      main.cust_request_no = dvh.cust_request_no\n" +
                "AND     dvh.rn = '1'\n" +
                "LEFT JOIN (\n" +
                "              SELECT  cust_request_no\n" +
                "                      ,change_type\n" +
                "                      ,approve_way\n" +
                "                      ,ROW_NUMBER() OVER(PARTITION BY cust_request_no ORDER BY created_stamp ASC ) AS rn\n" +
                "                      ,COUNT(cust_request_no) OVER(PARTITION BY cust_request_no ORDER BY created_stamp DESC) AS num\n" +
                "              FROM    datawarehouse_pro.dwd_loan_vehicle_info_change\n" +
                "              WHERE   ds = '${TXDATE}'\n" +
                "          ) dvhc\n" +
                "ON      main.cust_request_no = dvhc.cust_request_no\n" +
                "AND     dvhc.rn = '1' LEFT\n" +
                "JOIN    datawarehouse_pro.ads_f_retl_loan_schedule_busns afrlsb\n" +
                "ON      main.cust_request_no = afrlsb.cust_request_no\n" +
                "AND     afrlsb.ds = '${TXDATE}'\n" +
                "LEFT JOIN (\n" +
                "              SELECT  app_code\n" +
                "                      ,install_province_name\n" +
                "                      ,ROW_NUMBER() OVER(PARTITION BY app_code ORDER BY update_time DESC) AS rn\n" +
                "              FROM    datawarehouse_pro.dwd_ry_device\n" +
                "              WHERE   ds = '${TXDATE}'\n" +
                "          ) drd\n" +
                "ON      main.cust_request_no = drd.app_code\n" +
                "AND     drd.rn = '1' LEFT\n" +
                "JOIN    (\n" +
                "            SELECT  app_code\n" +
                "                    ,regexp_replace(\n" +
                "                        REPLACE(location_address,'中华人民共和国','')\n" +
                "                        ,'\\\\(.*?\\\\)|\\\\（.*?）'\n" +
                "                        ,''\n" +
                "                    ) AS location_address\n" +
                "                    ,mileage\n" +
                "                    ,duration\n" +
                "                    ,device_status\n" +
                "                    ,ROW_NUMBER() OVER(PARTITION BY app_code ORDER BY update_time DESC) AS rn\n" +
                "            FROM    datawarehouse_pro.dwd_ry_vehicle\n" +
                "            WHERE   ds = '${TXDATE}'\n" +
                "        ) drv\n" +
                "ON      main.cust_request_no = drv.app_code\n" +
                "AND     drv.rn = '1'\n" +
                "LEFT JOIN (\n" +
                "              SELECT  COUNT(1) AS num\n" +
                "                      ,cust_request_no\n" +
                "              FROM    datawarehouse_pro.dwd_out_bound_call_record\n" +
                "              WHERE   ds = '${TXDATE}'\n" +
                "              GROUP BY cust_request_no\n" +
                "          ) docr\n" +
                "ON      docr.cust_request_no = main.cust_request_no LEFT\n" +
                "JOIN    datawarehouse_pro.stg_credit_result_info_union scriu\n" +
                "ON      scriu.cust_request_no = main.cust_request_no\n" +
                "AND     scriu.ds = '${TXDATE}'\n" +
                "LEFT JOIN (\n" +
                "              SELECT  DISTINCT t2.car_batch_no\n" +
                "                      ,t3.apply_no AS cust_request_no\n" +
                "                      ,'Y' if_urgnt_flag    --是否进件加急订单 Y-是 N-否\n" +
                "              FROM    batchCarTmp t2\n" +
                "              LEFT JOIN datawarehouse_pro.dwd_rt_pre_eos1_apply_apov_dtl t3\n" +
                "              ON      t2.car_batch_no = t3.multi_vhcl_batch_no\n" +
                "              AND     t3.ds = '${TXDATE}'\n" +
                "          ) batchCar\n" +
                "ON      batchCar.cust_request_no = main.cust_request_no\n" +
                "left join  DatawareHouse_Pro.dwd_rt_credit_approve_status_info drtsi\n" +
                "on drtsi.apply_no=main.cust_request_no and drtsi.ds='${TXDATE}'\n" +
                ";");
        try {
            List<TableFieldVo> columns = getColumnNameBySQL(sql.toString());
            for (TableFieldVo column : columns) {
                System.out.println("Field: " + column.getFieldName() + ", Table: " + column.getTableName());
            }

            List<TableFieldVo> requestParams = getRequestParamsBySQL(sql.toString());
            for (TableFieldVo param : requestParams) {
                System.out.println("Request Param: " + param.getFieldName());
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }


}
